﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL.Entities;
using System.Data.SqlClient;

namespace DAL.EntitiesDAL
{
    public class TheDocGiaDAL : BaseDAL, UTL.IBaseDAL
    {
        #region Implement

        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            return ExecuteQuery("sp_SelectAll_Thedocgia");
        }

        public DataTable Select_TheDocGiaBiKhoa()
        {
            return ExecuteQuery("sp_Select_bikhoa");
        }

        public DataTable Select_TheDocGiaMuonSachQuaHan()
        {
            return ExecuteQuery("sp_Select_quahan");
        }

        public DataTable Select_TheDocGiaBiPhat()
        {
            return ExecuteQuery("sp_Select_DocGiaBiPhat");
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            var sql = "sp_Delete_Thedocgia {0}";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (TheDocGia)obj;
            var sql = "set dateformat dmy; execute sp_Insert_Thedocgia '{0}', N'{1}', '{2}', '{3}', '{4}', N'{5}', '{6}', N'{7}', N'{8}', '{9}', '{10}', '{11}', '{12}' ";
            sql = string.Format(sql, o.MADOCGIA, o.HODOCGIA, o.NGAYSINH.ToString("dd/MM/yyyy"), o.GIOITINH, o.SCMND, o.DIACHI, o.DIENTHOAI, o.NGHENGHIEP, o.NOICONGTAC, o.NGAYBATDAU.ToString("dd/MM/yyyy"), o.NGAYHETHAN.ToString("dd/MM/yyyy"), o.MALOAIDG, o.IMAGES);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (TheDocGia)obj;
            var sql = "set dateformat dmy; execute sp_Update_Thedocgia '{0}', N'{1}', '{2}', '{3}', N'{4}', '{5}', N'{6}', N'{7}', N'{8}', '{9}' ";
            sql = string.Format(sql, o.MADOCGIA, o.HODOCGIA,  o.GIOITINH, o.SCMND, o.DIACHI, o.DIENTHOAI, o.NGHENGHIEP, o.NOICONGTAC, o.MALOAIDG, o.IMAGES);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            var sql = @"Select *, (Case when GIOITINH=0 then 'Nam' else N'Nữ' end) as GIOITINH from tb_Thedocgia";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }

        public DataTable Search_MaThedocgia(string name)
        {
            var sql = @"set dateformat dmy; execute sp_Search_MaThedocgia '" + name +"' ";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }

        public DataTable Search_TenThedocgia(string name)
        {
            var sql = @"Select MADOCGIA, HODOCGIA + ' ' + TENDOCGIA as HODOCGIA, NGAYSINH, (Case when GIOITINH=0 then 'Nam' else N'Nữ' end) as GIOITINH, DIACHI, SCMND, DIENTHOAI, NGHENGHIEP, NOICONGTAC, NGAYBATDAU, NGAYHETHAN, MALOAIDG from tb_Thedocgia Where HODOCGIA + ' ' + TENDOCGIA like N'%" + name + "%'  ";
            sql = string.Format(sql, name);
            return ExecuteQuery(sql);
        }

        public DataTable Search(DAL.Entities.TheDocGia o)
        {
            string sql;
            if (o.MADOCGIA != "")
                sql = @"Select *,HODOCGIA + ' ' + TENDOCGIA as HODOCGIA, (Case when GIOITINH=0 then 'Nam' else N'Nữ' end) as Sex from tb_Thedocgia Where MADOCGIA like '%" + o.MADOCGIA + "%'  ";
            else
                sql = @"Select MADOCGIA, HODOCGIA + ' ' + TENDOCGIA as HODOCGIA, NGAYSINH, (Case when GIOITINH=0 then 'Nam' else N'Nữ' end) as GIOITINH, DIACHI, SCMND, DIENTHOAI, NGHENGHIEP, NOICONGTAC, NGAYBATDAU, NGAYHETHAN, MALOAIDG  from tb_Thedocgia Where (HODOCGIA like substring(N'" + o.HODOCGIA + "', 0, len(N'" + o.HODOCGIA + "') - len(Reverse(substring(Reverse(N'" + o.HODOCGIA + "'), 0, Patindex('% %',Reverse(N'" + o.HODOCGIA + "'))))) )  and TENDOCGIA like Reverse(substring(Reverse(N'" + o.HODOCGIA + "'), 0, Patindex('% %',Reverse(N'" + o.HODOCGIA + "'))) ) )  or DIACHI like N'" + o.DIACHI + "' or SCMND like N'" + o.SCMND + "' or DIENTHOAI like N'" + o.DIENTHOAI + "' or NGHENGHIEP like N'" + o.NGHENGHIEP + "' or NOICONGTAC like N'" + o.NOICONGTAC + "' or MALOAIDG like N'" + o.MALOAIDG + "' ";
               
            return ExecuteQuery(sql);
        }


        #endregion

        #region Other

        public DataTable Select_Max()
        {
            var sql = @"Select Max(MADOCGIA) as Ma From tb_Thedocgia";
            return ExecuteQuery(sql);
        }


        #endregion
    }
}
